﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Windows;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Database;
using ReflectionStudio.Core.Events;
using CAD2INTEX;
namespace WPFExcelReport
{
    public partial class CAD2INTEXData
    {

        public void SetUpCADTable()
        {
            CADTable.Add("TechMaster", null);
            CADTable.Add("TechDetail", null);
        }
        public DataSet QueryFINISHINGTECHMASTER()
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            SqlCommand sql;
            try
            {
                sql = SqlServerDB.CreateCommand();
                sql.CommandText = "SELECT * FROM techdb.dbo.FINISHINGTECHMASTER";
                setResult = Connect2Proxy(sql);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "FINISHINGTECHMASTER";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryLogInfor(string FInterID, string FProductNO)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            SqlCommand sql;
            try
            {
                sql = SqlServerDB.CreateCommand();
                sql.CommandText = "  SELECT TOP 200 max(l.FInterID) as FInterID, max(l.FDesignName) as FDesignName,max(l.FEditDate) as FEditDate,l.FProductNO,l.FClothNO "
                    + " FROM techdb.dbo.LogInfo l,techdb.dbo.TechMaster t "
                    + "  WHERE t.FClothNO=l.FProductNO and t.FColorNO=l.FClothNO and t.FIsDelete=0 and t.FIsCanAssess=1 and ( Left(Ltrim(l.FProductNO),1) ='D'  or Left(Ltrim(l.FProductNO),1) ='X'  or Left(Ltrim(l.FProductNO),1) ='Z'  or Left(Ltrim(l.FProductNO),1) ='G'  ) and  l.FInterID>" + FInterID
                    + " GROUP BY l.FProductNO,l.FClothNO"
                    + " ORDER BY 1";
                sql.CommandText = "  SELECT TOP 200 max(l.FInterID) as FInterID, max(l.FDesignName) as FDesignName,max(l.FEditDate) as FEditDate,l.FProductNO,l.FClothNO "
                    + " FROM techdb.dbo.LogInfo l,techdb.dbo.TechMaster t "
                    + "  WHERE t.FClothNO=l.FProductNO and t.FColorNO=l.FClothNO and t.FIsDelete=0  "
                    + " and ( Left(Ltrim(l.FProductNO),1) ='D'  or Left(Ltrim(l.FProductNO),1) ='X'  or Left(Ltrim(l.FProductNO),1) ='Z'  or Left(Ltrim(l.FProductNO),1) ='G' or Left(Ltrim(l.FProductNO),1) ='T'  ) and  l.FInterID>" + FInterID
                    + " AND l.FProductNO like '" + FProductNO+"%'"
                    //+ " AND l.FProductNO like 'Z3230A%'"
                    + " GROUP BY l.FProductNO,l.FClothNO"
                    + " ORDER BY 1";
                setResult = Connect2Proxy(sql);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "LogInfo";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryTechMaster(string FClothNO,string FColorNo)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            SqlCommand sql;
            try
            {

                sql = SqlServerDB.CreateCommand();

                sql.CommandText = "SELECT * FROM techdb.dbo.TechMaster WHERE  FIsDelete=0 and FIsCanAssess=1 and FClothNO=@FClothNO AND FColorNo=@FColorNo ";
                sql.CommandText = "SELECT * FROM techdb.dbo.TechMaster WHERE  FIsDelete=0 and FClothNO=@FClothNO AND FColorNo=@FColorNo ";
                sql.Parameters.Add("@FClothNO", FClothNO);
                sql.Parameters.Add("@FColorNo", FColorNo);
                setResult = Connect2Proxy(sql);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "TechMaster";
                }

                setReturn.Merge(setResult);
                foreach (DataRow dwTemp in tableLocal.Rows)
                {
                    string FMainID = dwTemp["FInterID"].ToString();
                    setResult = QueryTechDetail(FMainID);
                    setReturn.Merge(setResult);
                    setResult = QueryTechBeaming(FMainID);
                    setReturn.Merge(setResult);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryTechBeaming(string fMainID)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            SqlCommand sql;
            try
            {
                //SELECT [FInterID], [FMainID], [FBeamingName], [FChar], [FBeamingCode], [FBeamingLength], [FIsSlashingFlower], [FNicety], [FBeamingReedCount], [FBeamingCoefficient], [FBeamingStyle], [FLagSlashingWay], [FSmallSlashingWay], [FPatchOneTooth], [FSlashingWasteLength], [FMinToothCount], [FMaxToothCount], [FBeamingReedText], [FBeamingComments], [FOneTwistThreadCountParam] FROM [techdb].[dbo].[TechBeaming]
                sql = SqlServerDB.CreateCommand();
                sql.CommandText = "SELECT * FROM techdb.dbo.TechBeaming WHERE FMainID=@FMainID";
                sql.Parameters.Add("@FMainID", fMainID);
                setResult = Connect2Proxy(sql);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "TechBeaming";
                }

                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet QueryTechDetail(string fMainID)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataSet setTemp = new DataSet();
            DataTable tableLocal = null;
            SqlCommand sql;
            try
            {

                sql = SqlServerDB.CreateCommand();
                sql.CommandText = "SELECT * FROM techdb.dbo.TechDetail WHERE FMainID=@FMainID";
                sql.Parameters.Add("@FMainID", fMainID);
                setResult = Connect2Proxy(sql);
                if (setResult.Tables.Count > 0)
                {
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = "TechDetail";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
 
    }
}
